import pandas as pd
import json
from datetime import datetime

def extract_production_data(excel_path):
    """
    从Excel文件中提取生产线数据
    
    参数:
        excel_path: Excel文件路径
        
    返回:
        包含生产线数据的JSON对象
    """

    # 加载 Excel 文件
    df = pd.read_excel(excel_path, sheet_name=0, header=None)
    
    # 找到 "Production Line" 所在的单元格位置，并确认其右侧单元格为 "Project"
    target_cell = "Production Line"
    adjacent_cell = "Project"
    start_row, start_col = None, None
    
    for row in range(df.shape[0]):
        for col in range(df.shape[1]):
            # 检查当前单元格是否为目标单元格
            if df.iat[row, col] == target_cell:
                # 检查右侧单元格是否为预期值
                if col + 1 < df.shape[1] and df.iat[row, col + 1] == adjacent_cell:
                    start_row, start_col = row, col
                    break
        if start_row is not None:
            break
    
    if start_row is None:
        return {"error": "未找到包含 'Production Line' 且右侧为 'Project' 的单元格。"}
    
    # 提取从该单元格开始的表格
    data_section = df.iloc[start_row:, start_col:]
    
    # 将第一行作为列名
    data_section.columns = data_section.iloc[0]
    data_section = data_section[1:].reset_index(drop=True)
    
    result = []
    
    # 查找包含"Part Number"的列名，避免处理换行符
    part_number_col = None
    for col in data_section.columns:
        if isinstance(col, str) and "Part Number" in col:
            part_number_col = col
            break
    
    # 查找包含"Planned for the Week"的列名，这是周计划列
    weekly_planned_col = None
    for col in data_section.columns:
        if isinstance(col, str) and ("Planned" in col and "Week" in col):
            weekly_planned_col = col
            break
    
    for idx, row in data_section.iterrows():
        # 跳过空行
        if isinstance(row["Production Line"], float) and pd.isna(row["Production Line"]):
            continue
            
        production_line = ""
        if not pd.isna(row["Production Line"]):
            production_line = str(row["Production Line"]).strip()
        
        # 处理项目名称
        project = ""
        if "Project" in row and not pd.isna(row["Project"]):
            project = str(row["Project"]).strip()
        
        # 处理编号，列名包括换行符，此处做特殊处理
        part_number = ""
        if part_number_col and part_number_col in row and not pd.isna(row[part_number_col]):
            part_number = str(row[part_number_col]).strip()
        
        designation = ""
        if "Designation" in row and not pd.isna(row["Designation"]):
            designation = str(row["Designation"]).strip()
        
        # 获取每周计划
        weekly_plan = 0
        if weekly_planned_col and weekly_planned_col in row:
            weekly_plan_value = row[weekly_planned_col]
            
            if isinstance(weekly_plan_value, float) and pd.isna(weekly_plan_value):
                weekly_plan = 0
            elif isinstance(weekly_plan_value, str):
                weekly_plan_value = weekly_plan_value.strip()
                try:
                    weekly_plan = int(weekly_plan_value)
                except (ValueError, TypeError):
                    weekly_plan = 0
            else:
                try:
                    weekly_plan = int(weekly_plan_value)
                except (ValueError, TypeError):
                    weekly_plan = 0
        
        line_data = {
            "Production Line": production_line,
            "Project": project,
            "Part Number/Train NO": part_number,
            "Designation": designation,
            "Weekly Plan": weekly_plan
        }
        
        result.append(line_data)
    
    return result


if __name__ == "__main__":
    file_path = ""
    data = extract_production_data(file_path)
    
    # 输出JSON格式的结果
    print(json.dumps(data, ensure_ascii=False, indent=2))
